package br.com.sigad.dao;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.com.sigad.dto.AssociadoDTO;

public class AssociadoDAO {
	private String tabelaAssociado  = " GAD.GADTB001_ASSOCIADO ";
	private String tabelaUf		    = " GAD.GADTB004_UF ";
	
	private void preecherPK (AssociadoDTO socio, Connection conn)throws Exception{
		String sql = "select nextval ('gad.gadsq001_associado')";
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		rs.next();
		socio.setNuAssociado(rs.getBigDecimal(1));
	}
	
	public BigDecimal gravar(AssociadoDTO socio, Connection conn) throws Exception{
		String sql = " INSERT INTO" + tabelaAssociado +
		"(NU_EMPRESA, NU_ASSOCIADO, NO_ASSOCIADO, DE_CPF, DT_NASCIMENTO, NU_ESTADOCIVIL, DE_NATURALIDADE," +
		" NU_UFNATURALIDADE, DE_NACIONALIDADE, NO_PAIFILIACAO, DE_NACIONALIDADEPAI, NO_MAEFILIACAO," +
		" DE_NACIONALIDADEMAE, DE_ENDERECO, DE_CIDADE, NU_UFASSOCIADO, DE_TELEFONE, DE_MATRICULASIND," +
		" DT_ADMISSAOSIND, DE_CARTEIRATRAB, DE_SERIECARTEIRATRAB, NU_UFCARTEIRATRAB, DE_MATRICULAEMP," +
		" DT_ADMISSAOEMP, DE_LOTACAOEMP, DE_CHAPASECAOEMP, DE_CARGOEMP, DE_OBSERVACAO)" +
		" VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement ps = conn.prepareStatement(sql);
		int index = 1;
		preecherPK(socio, conn);
		ps.setBigDecimal(index++, socio.getEmpresa().getNuEmpresa());
		ps.setBigDecimal(index++, socio.getNuAssociado());
		ps.setString 	(index++, socio.getNoAssociado());
		ps.setString	(index++, socio.getdeCpf());
		ps.setDate		(index++, (java.sql.Date) socio.getDtNascimento());
		ps.setBigDecimal(index++, socio.getNuEstadoCivil());
		ps.setString 	(index++, socio.getDeNaturalidade());
		ps.setBigDecimal(index++, socio.getNuUfNaturalidade());
		ps.setString 	(index++, socio.getDeNacionalidade());
		ps.setString 	(index++, socio.getNoPaiFiliacao());
		ps.setString 	(index++, socio.getDeNacionalidadePai());
		ps.setString 	(index++, socio.getNoMaeFiliacao());
		ps.setString 	(index++, socio.getDeNacionalidadeMae());
		ps.setString 	(index++, socio.getDeEndereco());
		ps.setString 	(index++, socio.getDeCidade());
		ps.setBigDecimal(index++, socio.getNuUfAssociado());
		ps.setString	(index++, socio.getdeTelefone());
		ps.setString	(index++, socio.getdeMatriculaSind());
		ps.setDate	 	(index++, (java.sql.Date) socio.getDtAdmissaoSind());
		ps.setString	(index++, socio.getdeCarteiraTrab());
		ps.setString	(index++, socio.getdeSerieCarteiraTrab());
		ps.setBigDecimal(index++, socio.getNuUfCarteiraTrab());
		ps.setString	(index++, socio.getdeMatriculaEmp());
		ps.setDate	 	(index++, (java.sql.Date) socio.getDtAdmissaoEmp());
		ps.setString 	(index++, socio.getDeLotacaoEmp());
		ps.setString 	(index++, socio.getDeChapaSecaoEmp());
		ps.setString 	(index++, socio.getDeCargoEmp());
		ps.setString 	(index++, socio.getDeObservacao());
		
		ps.executeUpdate();
		return socio.getNuAssociado();
	}

	public void alterar(AssociadoDTO socio, Connection conn) throws Exception{		
		String sql = "UPDATE" + tabelaAssociado + "SET NU_EMPRESA = ?, NO_ASSOCIADO = ?, DE_CPF = ?, " +
					 "DT_NASCIMENTO = ?, NU_ESTADOCIVIL = ?, DE_NATURALIDADE = ?, NU_UFNATURALIDADE = ?, " +
					 "DE_NACIONALIDADE = ?, NO_PAIFILIACAO = ?, DE_NACIONALIDADEPAI = ?, NO_MAEFILIACAO = ?, " +
					 "DE_NACIONALIDADEMAE = ?, DE_ENDERECO = ?, DE_CIDADE = ?, NU_UFASSOCIADO = ?, DE_TELEFONE = ?, " +
					 "DE_MATRICULASIND = ?, DT_ADMISSAOSIND = ?, DE_CARTEIRATRAB = ?, DE_SERIECARTEIRATRAB = ?, " +
					 "NU_UFCARTEIRATRAB = ?, DE_MATRICULAEMP = ?, DT_ADMISSAOEMP = ?, DE_LOTACAOEMP = ?, " +
					 "DE_CHAPASECAOEMP = ?, DE_CARGOEMP = ?, DE_OBSERVACAO = ? " +
					 "WHERE NU_ASSOCIADO = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		int index = 1;
		ps.setBigDecimal(index++, socio.getEmpresa().getNuEmpresa());
		ps.setString 	(index++, socio.getNoAssociado());
		ps.setString	(index++, socio.getdeCpf());
		ps.setDate	 	(index++, (java.sql.Date)socio.getDtNascimento());
		ps.setBigDecimal(index++, socio.getNuEstadoCivil());
		ps.setString 	(index++, socio.getDeNaturalidade());
		ps.setBigDecimal(index++, socio.getNuUfNaturalidade());
		ps.setString 	(index++, socio.getDeNacionalidade());
		ps.setString 	(index++, socio.getNoPaiFiliacao());
		ps.setString 	(index++, socio.getDeNacionalidadePai());
		ps.setString 	(index++, socio.getNoMaeFiliacao());
		ps.setString 	(index++, socio.getDeNacionalidadeMae());
		ps.setString 	(index++, socio.getDeEndereco());
		ps.setString 	(index++, socio.getDeCidade());
		ps.setBigDecimal(index++, socio.getNuUfAssociado());
		ps.setString	(index++, socio.getdeTelefone());
		ps.setString	(index++, socio.getdeMatriculaSind());
		ps.setDate	 	(index++, (java.sql.Date) socio.getDtAdmissaoSind());
		ps.setString	(index++, socio.getdeCarteiraTrab());
		ps.setString	(index++, socio.getdeSerieCarteiraTrab());
		ps.setBigDecimal(index++, socio.getNuUfCarteiraTrab());
		ps.setString	(index++, socio.getdeMatriculaEmp());
		ps.setDate	 	(index++, (java.sql.Date) socio.getDtAdmissaoEmp());
		ps.setString 	(index++, socio.getDeLotacaoEmp());
		ps.setString 	(index++, socio.getDeChapaSecaoEmp());
		ps.setString 	(index++, socio.getDeCargoEmp());
		ps.setString 	(index++, socio.getDeObservacao());
		ps.setBigDecimal(index++, socio.getNuAssociado());
		ps.executeUpdate();
		ps.close();
	}

	public void excluir(AssociadoDTO socio, Connection conn) throws Exception{
		
		String sql = "DELETE FROM" + tabelaAssociado + "WHERE NU_ASSOCIADO = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		int index = 1;
		ps.setBigDecimal(index++, socio.getNuAssociado());
		ps.executeUpdate();
		ps.close();
	}
	
	public List<AssociadoDTO> pesquisar(AssociadoDTO socio, Connection conn) throws Exception{
		List<AssociadoDTO> socios = new ArrayList<AssociadoDTO>();
		String sql = "SELECT ASSOC.NU_EMPRESA, ASSOC.NU_ASSOCIADO, ASSOC.NO_ASSOCIADO, ASSOC.DE_CPF, " +
					 "ASSOC.DT_NASCIMENTO, ASSOC.NU_ESTADOCIVIL, ASSOC.DE_NATURALIDADE, ASSOC.NU_UFNATURALIDADE, " +
					 "ASSOC.DE_NACIONALIDADE, ASSOC.NO_PAIFILIACAO, ASSOC.DE_NACIONALIDADEPAI, ASSOC.NO_MAEFILIACAO, " +
					 "ASSOC.DE_NACIONALIDADEMAE, ASSOC.DE_ENDERECO, ASSOC.DE_CIDADE, ASSOC.NU_UFASSOCIADO, " +
					 "ASSOC.DE_TELEFONE, ASSOC.DE_MATRICULASIND, ASSOC.DT_ADMISSAOSIND, ASSOC.DE_CARTEIRATRAB, " +
					 "ASSOC.DE_SERIECARTEIRATRAB, ASSOC.NU_UFCARTEIRATRAB, ASSOC.DE_MATRICULAEMP, " +
					 "ASSOC.DT_ADMISSAOEMP, ASSOC.DE_LOTACAOEMP, ASSOC.DE_CHAPASECAOEMP, ASSOC.DE_CARGOEMP, " +
					 "ASSOC.DE_OBSERVACAO, UF_NATURALIDADE.SG_UF " +
					 "FROM " + tabelaAssociado + " ASSOC " +
					 "INNER JOIN " + tabelaUf + 
					 "UF_NATURALIDADE ON (ASSOC.NU_UFNATURALIDADE = UF_NATURALIDADE.NU_SG_UF) " +
					 " WHERE UPPER (NO_ASSOCIADO) like UPPER (?) ORDER BY NO_ASSOCIADO";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, socio.getNoAssociado()+"%");
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			AssociadoDTO socioResultado = new AssociadoDTO();
			socioResultado.getEmpresa().setNuEmpresa(rs.getBigDecimal("NU_EMPRESA"));
			socioResultado.setNuAssociado			(rs.getBigDecimal("NU_ASSOCIADO"));
			socioResultado.setNoAssociado			(rs.getString("NO_ASSOCIADO"));
			socioResultado.setdeCpf					(rs.getString("DE_CPF"));
			socioResultado.setDtNascimento			(rs.getDate("DT_NASCIMENTO"));
			socioResultado.setNuEstadoCivil			(rs.getBigDecimal("NU_ESTADOCIVIL"));
			socioResultado.setDeNaturalidade		(rs.getString("DE_NATURALIDADE"));
			socioResultado.setNuUfNaturalidade		(rs.getBigDecimal("NU_UFNATURALIDADE"));
			socioResultado.setDeNacionalidade		(rs.getString("DE_NACIONALIDADE"));
			socioResultado.setNoPaiFiliacao			(rs.getString("NO_PAIFILIACAO"));
			socioResultado.setDeNacionalidadePai	(rs.getString("DE_NACIONALIDADEPAI"));
			socioResultado.setNoMaeFiliacao			(rs.getString("NO_MAEFILIACAO"));
			socioResultado.setDeNacionalidadeMae	(rs.getString("DE_NACIONALIDADEMAE"));
			socioResultado.setDeEndereco			(rs.getString("DE_ENDERECO"));
			socioResultado.setDeCidade				(rs.getString("DE_CIDADE"));
			socioResultado.setNuUfAssociado			(rs.getBigDecimal("NU_UFASSOCIADO"));
			socioResultado.setdeTelefone			(rs.getString("DE_TELEFONE"));
			socioResultado.setdeMatriculaSind		(rs.getString("DE_MATRICULASIND"));
			socioResultado.setDtAdmissaoSind		(rs.getDate("DT_ADMISSAOSIND"));
			socioResultado.setdeCarteiraTrab		(rs.getString("DE_CARTEIRATRAB"));
			socioResultado.setdeSerieCarteiraTrab	(rs.getString("DE_SERIECARTEIRATRAB"));
			socioResultado.setNuUfCarteiraTrab		(rs.getBigDecimal("NU_UFCARTEIRATRAB"));
			socioResultado.setdeMatriculaEmp		(rs.getString("DE_MATRICULAEMP"));
			socioResultado.setDtAdmissaoEmp			(rs.getDate("DT_ADMISSAOEMP"));
			socioResultado.setDeLotacaoEmp			(rs.getString("DE_LOTACAOEMP"));
			socioResultado.setDeChapaSecaoEmp		(rs.getString("DE_CHAPASECAOEMP"));
			socioResultado.setDeCargoEmp			(rs.getString("DE_CARGOEMP"));
			socioResultado.setDeObservacao			(rs.getString("DE_OBSERVACAO"));
			socioResultado.setSgUfNaturalidade      (rs.getString("SG_UF"));
			socios.add(socioResultado);
		}
		return socios;
	}

	public List<AssociadoDTO> verificaDuplicidade(Connection conn, AssociadoDTO socio) throws SQLException {
		List<AssociadoDTO> socios = new ArrayList<AssociadoDTO>();
		String sql = " SELECT DE_CPF from" + tabelaAssociado +
					 " WHERE  DE_CPF = '" + socio.getdeCpf().toString() + "'";
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			AssociadoDTO socioResultado = new AssociadoDTO();
			socioResultado.setdeCpf		(rs.getString("DE_CPF"));
			socios.add(socioResultado);
		}
		return socios;
	}

	public List<AssociadoDTO> pesquisarTodosAposentados(Connection conn)throws SQLException {
		List<AssociadoDTO> sociosAposentados = new ArrayList<AssociadoDTO>();
		
		String sql = "SELECT ASSOC.NU_ASSOCIADO, ASSOC.NO_ASSOCIADO, ASSOC.DE_CPF, " +
		 "ASSOC.DT_NASCIMENTO, ASSOC.DE_NACIONALIDADE, ASSOC.DE_ENDERECO, ASSOC.DE_CIDADE, ASSOC.NU_UFASSOCIADO, " +
		 "ASSOC.DE_TELEFONE, ASSOC.DE_MATRICULASIND, ASSOC.DT_ADMISSAOSIND, ASSOC.DE_CARTEIRATRAB, " +
		 "ASSOC.DE_SERIECARTEIRATRAB, ASSOC.NU_UFCARTEIRATRAB, ASSOC.DE_OBSERVACAO, UF_NATURALIDADE.SG_UF " +
		 "FROM " + tabelaAssociado + " ASSOC " +
		 "INNER JOIN " + tabelaUf + 
		 "UF_NATURALIDADE ON (ASSOC.NU_UFNATURALIDADE = UF_NATURALIDADE.NU_SG_UF) " +
		 " WHERE UPPER (DE_OBSERVACAO) like UPPER (?) ORDER BY NO_ASSOCIADO";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		String paramApos = "apos";
		String paramTad = "tad";
		ps.setString(1, "%" + paramApos + "%" + paramTad + "%");
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			AssociadoDTO aposentadoResultado = new AssociadoDTO();
			aposentadoResultado.setNuAssociado			(rs.getBigDecimal("NU_ASSOCIADO"));
			aposentadoResultado.setNoAssociado			(rs.getString("NO_ASSOCIADO"));
			aposentadoResultado.setdeCpf				(rs.getString("DE_CPF"));
			aposentadoResultado.setDtNascimento			(rs.getDate("DT_NASCIMENTO"));
			aposentadoResultado.setDeNacionalidade		(rs.getString("DE_NACIONALIDADE"));
			aposentadoResultado.setDeEndereco			(rs.getString("DE_ENDERECO"));
			aposentadoResultado.setDeCidade				(rs.getString("DE_CIDADE"));
			aposentadoResultado.setNuUfAssociado		(rs.getBigDecimal("NU_UFASSOCIADO"));
			aposentadoResultado.setdeTelefone			(rs.getString("DE_TELEFONE"));
			aposentadoResultado.setdeMatriculaSind		(rs.getString("DE_MATRICULASIND"));
			aposentadoResultado.setDtAdmissaoSind		(rs.getDate("DT_ADMISSAOSIND"));
			aposentadoResultado.setdeCarteiraTrab		(rs.getString("DE_CARTEIRATRAB"));
			aposentadoResultado.setdeSerieCarteiraTrab	(rs.getString("DE_SERIECARTEIRATRAB"));
			aposentadoResultado.setNuUfCarteiraTrab		(rs.getBigDecimal("NU_UFCARTEIRATRAB"));
			aposentadoResultado.setDeObservacao			(rs.getString("DE_OBSERVACAO"));
			aposentadoResultado.setSgUfNaturalidade     (rs.getString("SG_UF"));
			sociosAposentados.add(aposentadoResultado);
		}
		return sociosAposentados;
	}
}